package org.halk;

import cn.hutool.core.io.resource.ResourceUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.halk.entity.StatisticPeriodWeekInfoExportBO;
import org.halk.entity.StatisticsPeriodTotal;
import org.junit.Test;

import java.io.*;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * easy excel模板导出
 * @Author zhuhao
 * @Date 2024/4/26 11:12
 * @Description
 */
@Slf4j
public class ExportTemplateExcel {


    @Test
    public void test1(){
        //获取所有的sheet页名称
        List<String> sheetNameList = getSheetNameList();
        //获取结果集
        Map<String, Map<String, Object>> resultMap = getResultMap();

        String fileName = "各站点周度采集进度监控表_" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
//        exportMoreSheet(response, sheetNameList, fileName, resultMap);
        exportMoreSheet(sheetNameList, fileName, resultMap);
    }

    /**
     * 获取结果集
     */
    private Map<String, Map<String, Object>> getResultMap() {
        Map<String, Map<String, Object>> resultMap = new HashMap<>();

        //一个站点当月的所有周数据
        List<StatisticPeriodWeekInfoExportBO> monthWeekInfoList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            StatisticPeriodWeekInfoExportBO weekInfo = new StatisticPeriodWeekInfoExportBO()
                    .setWeekStr("第" + i + "周")
                    .setClctGap(100)
                    .setClctNum(100)
                    .setCustomClct(100)
                    .setCustomMriClct(100)
                    .setMriClct(100)
                    .setMritClctGapSum(100)
                    .setGapReason("原因")
                    .setRemark("备注");
            monthWeekInfoList.add(weekInfo);
        }
        StatisticsPeriodTotal total = new StatisticsPeriodTotal().setProjectLeader("张三")
                .setProjectNum(1)
                .setSiteId("北京大学")
                .setSiteName("北京大学")
                .setMonthStr("2025-03-03")
                .setRecruitTarget(100)
                .setInventoryNum(100)
                .setClctNum(100)
                .setMriClctNum(100)
                .setMriClctGap(100)
                .setRecruitSchoolNum(100)
                .setRecruitSchoolAverage(100)
                .setAssertGap(100)
                .setRemark("备注");

        for (int i = 0; i < 5; i++) {
            Map<String, Object> map = new HashMap<>();
            map.put("firstHeadName", "课题" + i + "-" + "北京大学站点");
            map.put("totalList", Collections.singletonList(total));
            map.put("weekInfoBOList", monthWeekInfoList);
            //获取最后的更新时间
            map.put("maxUpdateTime", LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));

            resultMap.put("第" + i + "页", map);
        }

        return resultMap;
    }

    /**
     * 提前获取所有的sheet页名称
     * @return
     */
    private List<String> getSheetNameList() {
        //获取所有的sheet页名称
        List<String> sheetNameList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            sheetNameList.add("第" + i + "页");
        }

        return sheetNameList;
    }


    //    private void exportMoreSheet(HttpServletResponse response, List<String> sheetNameList, String fileName, Map<String, Map<String, Object>> resultMap) {
    private void exportMoreSheet(List<String> sheetNameList, String fileName, Map<String, Map<String, Object>> resultMap) {
        if (CollectionUtils.isEmpty(sheetNameList)) {
            sheetNameList = Collections.singletonList("暂无数据");
        }
        //获取模板路径
        String fullFilePath = "file" + File.separator + "export" + File.separator + "template" + File.separator + "statisticPeriodTotal.xlsx";
        InputStream inputStream = ResourceUtil.getResourceObj(fullFilePath).getStream();
        //一个模板多个sheet页使用，需要克隆
        InputStream newIns = cloneSheet(inputStream, 0, sheetNameList);
        //如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();

        ExcelWriter excelWriter = null;
        try {
            String excelName = URLEncoder.encode(fileName, "UTF-8");
//            response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
//            log.info("response:{}", response);
//            response.setContentType("application/vnd.ms-excel");
//            response.setCharacterEncoding("utf-8");
//            excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(newIns).build();
            excelWriter = EasyExcel.write("D:\\" + fileName + ".xlsx").withTemplate(newIns).build();

            for (String sheetName : sheetNameList) {
                Map<String, Object> map = resultMap.getOrDefault(sheetName, new HashMap<>());

                WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
                excelWriter.fill(new FillWrapper("total", (List<StatisticsPeriodTotal>) map.get("totalList")), fillConfig, writeSheet);
                excelWriter.fill(new FillWrapper("weekInfo", (List<StatisticPeriodWeekInfoExportBO>) map.get("weekInfoBOList")), fillConfig, writeSheet);

                excelWriter.fill(map, writeSheet);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            //不要忘记执行这个方法,关闭流,执行后才能真正填充数据
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 克隆sheet
     * 注意传入的文件流在执行本方法后将关闭
     * @param excelIns excel文件流
     * @param tplSheetIndex 需要克隆的模板索引
     * @param newSheetNames 所需要生成的excel最终的sheet名
     * @return 新的InputStream
     */
    @SneakyThrows
    public InputStream cloneSheet(InputStream excelIns, int tplSheetIndex, List<String> newSheetNames) {
        Workbook workbook = new XSSFWorkbook(excelIns);
        Sheet tplSheet = workbook.getSheetAt(tplSheetIndex);//模板sheet
        for (int i = 0; i < newSheetNames.size(); i++) {
            String sheetName = newSheetNames.get(i);
            if (0 == i) {//第一个,直接改名即可
                workbook.setSheetName(0, sheetName);
            } else {
                Sheet newSheet = workbook.cloneSheet(0);
                //同时复制打印设置
                PrintSetup tplPrintSetup = tplSheet.getPrintSetup();
                PrintSetup newPrintSetup = newSheet.getPrintSetup();
                newPrintSetup.setLandscape(tplPrintSetup.getLandscape());//打印方向，true：横向，false：纵向(默认)
                newPrintSetup.setPaperSize(tplPrintSetup.getPaperSize());//纸张类型

                int sheetIndex = workbook.getSheetIndex(newSheet);
                workbook.setSheetName(sheetIndex, sheetName);
            }
        }
        try (ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
            workbook.write(bos);
            byte[] bytes = bos.toByteArray();
            InputStream ins = new ByteArrayInputStream(bytes);
            return ins;
        } finally {
            if (null != excelIns) excelIns.close();
        }

    }
}
